package com.laiketui.common.utils.tool.file;

import com.alibaba.csp.sentinel.util.StringUtil;
import com.alibaba.fastjson.JSON;
import com.laiketui.common.utils.tool.PriceUtils;
import com.laiketui.common.utils.tool.base.PathUtil;
import com.laiketui.common.utils.tool.str.StringUtils;
import com.laiketui.core.common.SplitUtils;
import com.laiketui.core.exception.LaiKeAPIException;
import com.laiketui.core.lktconst.GloabConst;
import com.laiketui.core.utils.tool.DataUtils;
import com.laiketui.domain.lktconst.ErrorCode;
import com.laiketui.domain.vo.MainVo;
import com.laiketui.domain.vo.Tool.ExcelAnalysisVo;
import com.laiketui.domain.vo.Tool.ExcelParamVo;
import org.apache.commons.collections.MapUtils;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.util.Units;
import org.apache.poi.xssf.usermodel.*;
import org.springframework.web.multipart.MultipartFile;

import javax.servlet.http.HttpServletResponse;
import java.io.*;
import java.net.HttpURLConnection;
import java.net.URL;
import java.net.URLDecoder;
import java.util.*;

/**
 * exceld导出工具类
 *
 * @author Trick
 * @date 2021/7/14 10:46
 */
public class EasyPoiExcelUtil {
    /**
     * 导出excel
     *
     * @param vo -
     * @throws LaiKeAPIException-
     * @author Trick
     * @date 2021/7/29 16:07
     */
    public static void excelExport(ExcelParamVo vo) throws LaiKeAPIException {
        try {
            if (vo.getResponse() == null) {
                throw new LaiKeAPIException(ErrorCode.BizErrorCode.PARAMATER_ERROR, "参数错误");
            }
            if (StringUtils.isEmpty(vo.getTitle())) {
                throw new LaiKeAPIException(ErrorCode.BizErrorCode.PARAMATER_ERROR, "参数错误");
            }
            if (vo.getList() == null) {
                vo.setList(new ArrayList<>());
            }
            if (vo.getHeaderList() == null || vo.getHeaderList().length < 1) {
                throw new LaiKeAPIException(ErrorCode.BizErrorCode.PARAMATER_ERROR, "参数错误");
            }
            if (vo.getHeaderList().length != vo.getHeaderList().length) {
                throw new LaiKeAPIException(ErrorCode.BizErrorCode.PARAMATER_ERROR, "数据内容和表头数量不匹配");
            }

            //创建Excel工作簿对象
            HSSFWorkbook workbook = new HSSFWorkbook();
            HSSFSheet sheet = workbook.createSheet(vo.getTitle());
            //设置表头样式
            HSSFCellStyle style = workbook.createCellStyle();
            style.setAlignment(HorizontalAlignment.CENTER);
            style.setVerticalAlignment(VerticalAlignment.CENTER);

            //表头开始位置
            int topTitleIndex = 0;
            //顶部合并单元格-说明专用
            if (vo.getMerge() != null && vo.getMerge().length == 4) {
                //顶部占一行
                HSSFRow rowTop = sheet.createRow(topTitleIndex++);
                //设置表头高度
                rowTop.setHeightInPoints(30);
                //设置内容
                rowTop.createCell(0).setCellStyle(style);
                rowTop.getCell(0).setCellValue(vo.getTopTitle());
                //合并单元格
                CellRangeAddress region = new CellRangeAddress(vo.getMerge()[0], vo.getMerge()[1], vo.getMerge()[2], vo.getMerge()[3]);
                sheet.addMergedRegion(region);
            }

            //创建表头
            HSSFRow row = sheet.createRow(topTitleIndex);
            //设置表头高度
            row.setHeightInPoints(30);
            //生成序号
            if (vo.isNeedNo()) {
                List<String> headerTempList = new ArrayList<>(Arrays.asList(vo.getHeaderList()));
                headerTempList.add(0, "序号");
                vo.setHeaderList(headerTempList.toArray(new String[0]));
                List<String> headerTempValueList = new ArrayList<>(Arrays.asList(vo.getValueList()));
                vo.setValueList(headerTempValueList.toArray(new String[0]));
            }
            //生成自定义表头
            for (int i = 0; i < vo.getHeaderList().length; i++) {
                row.createCell(i).setCellStyle(style);
                row.getCell(i).setCellValue(vo.getHeaderList()[i]);
            }

            //当前行对应的key
            List<String> valList = DataUtils.convertToList(vo.getValueList());
            if (valList == null) {
                throw new LaiKeAPIException(ErrorCode.BizErrorCode.PARAMATER_ERROR, "参数错误");
            }
            //设置内容
            for (int i = 0; i < vo.getList().size(); i++) {
                //当前找到的数量
                int num = 0;
                //生成当前行
                HSSFRow fieldRow = sheet.createRow(i + topTitleIndex + 1);
                Map<String, Object> dataMap = vo.getList().get(i);
                //循环获取当前列数据
                for (String keyTemp : dataMap.keySet()) {
                    if (num == valList.size()) {
                        break;
                    }
                    //获取当前行数据
                    if (valList.contains(keyTemp)) {
                        //当前行对应的值的坐标
                        int index = valList.indexOf(keyTemp);
                        //获取当前x坐标数据
                        String rowValue = dataMap.get(keyTemp) + "";
                        if (StringUtils.isEmpty(rowValue)) {
                            rowValue = "";
                        }
                        //生成序号,序号每行只生成一个
                        if (vo.isNeedNo()) {
                            if (num == 0) {
                                fieldRow.createCell(0).setCellStyle(style);
                                fieldRow.getCell(0).setCellValue(i + 1);
                            }
                            //加了序号,所以每行偏移一个位置
                            index++;
                        }
                        //创建行 x坐标>样式/值
                        fieldRow.createCell(index).setCellStyle(style);
                        fieldRow.getCell(index).setCellValue(rowValue);
                        num++;
                    }
                }
            }
            for (int i = 0; i < vo.getHeaderList().length; i++) {
                //设置自适应宽度
                sheet.autoSizeColumn(i);
            }
            //设置sheet页
            workbook.setActiveSheet(0);

            vo.getResponse().setContentType("application/msexcel");
            String title = String.format("%s.xls", vo.getTitle());
            vo.getResponse().addHeader("Content-Disposition", "filename=" + URLDecoder.decode(title, GloabConst.Chartset.UTF8));
            OutputStream outputStream = vo.getResponse().getOutputStream();

            workbook.write(outputStream);
            outputStream.close();
            workbook.close();
        } catch (Exception e) {
            e.printStackTrace();
            throw new LaiKeAPIException(ErrorCode.BizErrorCode.FILE_EXCEL_ERROR, "EXCEL文件导出失败", "excelExport");
        }
    }

    @Deprecated
    public static void excelExport(String title, String[] headerList, String[] valueList, List<Map<String, Object>> list, HttpServletResponse response) throws LaiKeAPIException {
        ExcelParamVo vo = new ExcelParamVo();
        vo.setTitle(title);
        vo.setHeaderList(headerList);
        vo.setValueList(valueList);
        vo.setList(list);
        vo.setResponse(response);
        excelExport(vo);
    }

    /**
     * 解析excel
     *
     * @param vo -
     * @return List
     * @throws LaiKeAPIException-
     * @author Trick
     * @date 2021/12/7 15:41
     */
    public static Map<String, Object> analysisExcel(ExcelAnalysisVo vo) throws LaiKeAPIException {
        Map<String, Object> resultMap = new HashMap<>(16);
        try {
            List<Map<String, Object>> resultList = new ArrayList<>();
            for (MultipartFile file : vo.getFile()) {
                File fileTemp = multipartFileToFile(file);
                if (fileTemp == null) {
                    throw new LaiKeAPIException(ErrorCode.BizErrorCode.FILE_EXCEL_NOT_EXISTS, "文件不存在", "analysisExcel");
                }
                //第一个参数表示行数 第二个List保存该行的cell数据
                Map<Integer, List<String>> map = new LinkedHashMap<>(16);
                try (FileInputStream fis = new FileInputStream(fileTemp)) {
                    Workbook workBook = new XSSFWorkbook(fis);
                    // 获取第一个sheet
                    Sheet sheet = workBook.getSheetAt(0);
                    //标题数量
                    int titleNum = 0;
                    int lineIndex = 0;
                    for (Row row : sheet) {
                        if (titleNum == 0) {
                            //获取有效标题数量
                            for (Cell cell : row) {
                                titleNum++;
                            }
                        }
                        map.put(lineIndex, new ArrayList<>());
                        //循环取值
                        for (int i = 0; i < titleNum; i++) {
                            DataFormatter formatter = new DataFormatter();
                            map.get(lineIndex).add(formatter.formatCellValue(row.getCell(i)));
                        }
                        lineIndex++;
                    }
                }catch (Exception e){
                    throw new LaiKeAPIException(ErrorCode.BizErrorCode.FILE_EXCEL_READ_ERROR, "EXCEL文件解析失败", "analysisExcel");
                }
                    // 以下为遍历 Map解析结果
                    Set<Integer> keys = map.keySet();
                    if (keys.size() <= 0){
                        throw new LaiKeAPIException(ErrorCode.BizErrorCode.ERROR_CODE_QWTJKBWJ, "请勿提交空白文件");
                    }
                    currentExcel:
                    //循环每行
                    for (Integer key : keys) {
                        //获取当前行列值
                        List<String> list = map.get(key);
                        //当前列
                        int index = 0;
                        //失败原因
                        StringBuilder errorText = new StringBuilder();
                        //组装每列内容
                        Map<String, Object> excelMap = new HashMap<>(1);
                        excelMap.put("fileName", file.getOriginalFilename());
                        //循环当前行每列
                        for (String s : list) {
                            if (key == 0) {
                                if (vo.getTitleName() == null || list.size() != vo.getTitleName().size()) {
                                    errorText.append(String.format("第%s行第%s列", key + 1, index + 1)).append(" 标题参数错误").append(SplitUtils.DH);
                                }
                                if (vo.getTitleName() == null || !s.equals(vo.getTitleName().get(index))) {
                                    errorText.append(String.format("第%s行第%s列", key + 1, index + 1)).append(" 请按照模板填写").append(SplitUtils.DH);
                                }
                                if (StringUtils.isNotEmpty(errorText)) {
                                    excelMap.put("errorText", errorText);
                                    resultList.add(excelMap);
                                    break currentExcel;
                                }
                            }
                            if (key > 0) {
                                if (vo.getValueKey() == null || list.size() != vo.getValueKey().size()) {
                                    errorText.append(String.format("第%s行第%s列", key + 1, index)).append(" 内容数据错误").append(SplitUtils.DH);
                                }
                                excelMap.put(vo.getValueKey().get(index), s);
                                excelMap.put("x", key + 1);
                                excelMap.put("y", index + 1);
                            }
                            index++;
                        }
                        excelMap.put("errorText", errorText);
                        resultList.add(excelMap);
                    }
            }
            resultMap.put("list", resultList);
        }  catch (LaiKeAPIException l) {
            throw l;
        } catch (Exception e) {
            throw new LaiKeAPIException(ErrorCode.BizErrorCode.ERROR_CODE_WLYC, "网络异常", "batchDelivery");
        }
        return resultMap;
    }

    public static File multipartFileToFile(MultipartFile multiFile) {
        // 获取文件名
        String fileName = multiFile.getOriginalFilename();
        if (fileName == null) {
            fileName = "";
        }
        try {
            // 获取文件后缀
            String prefix = fileName.substring(fileName.lastIndexOf("."));
            // 若须要防止生成的临时文件重复,能够在文件名后添加随机码
            File file = File.createTempFile(fileName, prefix);
            multiFile.transferTo(file);
            return file;
        } catch (Exception e) {
            e.printStackTrace();
        }
        return null;
    }

    /**
     * 导出购物车清单,供货价开关打开用供应商导出模板
     * @param response
     * @param supplierOpen
     * @throws LaiKeAPIException
     */
    public static void goodsExcel(HttpServletResponse response, String roleDiscount, Integer supplierOpen,String content) throws LaiKeAPIException{
        try {
            String path = PathUtil.getClassesPath() + "file/goods.xlsx";
            //供货价按钮打开读取供货商模板
            if(supplierOpen !=null && supplierOpen == 1){
                path = PathUtil.getClassesPath() + "file/supplierGoods.xlsx";
            }
//            POIFSFileSystem pfs = new POIFSFileSystem(new FileInputStream(path));

            XSSFWorkbook wb = new XSSFWorkbook(new FileInputStream(path));
            HttpURLConnection connection = null;
//            List<PageData> pdList = JSON.parseArray(strList,PageData.class); //转化为List
            Sheet sheet = wb.getSheetAt(0);
            wb.setSheetName(0,"产品销售表");
            Row row = sheet.getRow(1);
            CellStyle style = sheet.getRow(6).getCell(0).getCellStyle();
            style.setWrapText(true);
            int rows = 5;
            Integer totalNum = 0;
            Integer totalMoney = 0;
            //供货价总价
            Integer totalSupplierPrice = 0;

//            String strList = "[{\"area\":\"客厅\",\"picture\":\"https://luockoo.oss-cn-shenzhen.aliyuncs.com/1/7/20240727/1817105535447609344.png\",\"categoryName\":\"沙发\",\"productCode\":\"Z-ZMS-YY-148-CJ\"" +
//                    ",\"parameters\":\"亮光卡塔金_茶几1300*700*370MM\",\"marque\":\"\",\"material\":\"岩板+实木+中纤板+钢化玻璃+缓冲导轨\",\"num\":2,price:100},{\"area\":\"客厅\",\"picture\":\"https://luockoo.oss-cn-shenzhen.aliyuncs.com/1/7/20240727/1817105535447609344.png\",\"label\":\"沙发\",\"code\":\"Z-ZMS-YY-148-CJ\"" +
//                    ",\"parameters\":\"亮光卡塔金_茶几1300*700*370MM\",\"marque\":\"\",\"material\":\"岩板+实木+中纤板+钢化玻璃+缓冲导轨\",\"num\":2,price:100}]";
            List<Map> childsList = JSON.parseArray(content, Map.class);

            sheet.shiftRows(5, sheet.getLastRowNum(), childsList.size());
            for (int i = 0; i < childsList.size(); i++) {
                if (0 != i) {
                    rows++;
                }
                URL url = new URL(childsList.get(i).get("picture").toString());
                connection = (HttpURLConnection)url.openConnection();
                connection.setRequestMethod("GET");
                connection.setConnectTimeout(50000);
                InputStream inputStream = connection.getInputStream();
                byte[] data = readInputStream(inputStream);
                row = sheet.createRow(rows);
                Cell cell = row.createCell(0);
                cell.setCellStyle(style);
                cell.setCellValue(MapUtils.getString(childsList.get(i),"area"));
                row.setHeight((short)2000);
                Cell cell2 = row.createCell(1);
                int pictureIdx = wb.addPicture(data, Workbook.PICTURE_TYPE_JPEG);
                CreationHelper helper = wb.getCreationHelper();
                ClientAnchor anchor = helper.createClientAnchor();
                Drawing drawing = sheet.createDrawingPatriarch();
                //居中图片
                anchor.setRow1(row.getRowNum());
                anchor.setRow2(row.getRowNum() + 1);
                anchor.setCol1(cell2.getColumnIndex());
                anchor.setCol2(cell2.getColumnIndex() + 1);
                anchor.setDx1(Units.EMU_PER_PIXEL * 20);
                anchor.setDx2(Units.EMU_PER_PIXEL * (-20));
                anchor.setDy1(Units.EMU_PER_PIXEL * 20);
                anchor.setDy2(Units.EMU_PER_PIXEL * (-20));
                drawing.createPicture(anchor, pictureIdx);
                cell2.setCellStyle(style);
                Cell cell3 = row.createCell(2);
                cell3.setCellStyle(style);
                cell3.setCellValue(MapUtils.getString(childsList.get(i),"categoryName"));
                Cell cell4 = row.createCell(3);
                cell4.setCellStyle(style);
                String productCode = MapUtils.getString(childsList.get(i), "productCode");
                productCode = URLDecoder.decode(productCode, GloabConst.Chartset.UTF8);
                cell4.setCellValue(productCode);
                Cell cell5 = row.createCell(4);
                cell5.setCellStyle(style);
                String marque = MapUtils.getString(childsList.get(i), "marque");
                marque = URLDecoder.decode(marque, GloabConst.Chartset.UTF8);
                cell5.setCellValue(marque);
                Cell cell6 = row.createCell(5);
                cell6.setCellStyle(style);
                String parameters = MapUtils.getString(childsList.get(i), "parameters");
                parameters = URLDecoder.decode(parameters, GloabConst.Chartset.UTF8);
                cell6.setCellValue(parameters);
                Cell cell7 = row.createCell(6);
                cell7.setCellStyle(style);
                String material = MapUtils.getString(childsList.get(i), "material");
                material = URLDecoder.decode(material, GloabConst.Chartset.UTF8);
                cell7.setCellValue(material);
                Cell cell8 = row.createCell(7);
                cell8.setCellStyle(style);
                String num = MapUtils.getString(childsList.get(i),"num");
                if(StringUtils.isNotEmpty(num)){
                    cell8.setCellValue(Integer.valueOf(num));
                }
                String price = MapUtils.getString(childsList.get(i), "price");
                Cell cell9 = row.createCell(8);
                cell9.setCellStyle(style);
                if(StringUtils.isNotEmpty(price)){
                    cell9.setCellValue(Integer.valueOf(price));
                }
                //销售总价
                Cell cell10 = row.createCell(9);
                cell10.setCellStyle(style);
                if(StringUtil.isNotBlank(num) || StringUtil.isNotBlank(price)){
                    cell10.setCellValue(PriceUtils.mul2Integer(num,price));
                }
                //供货价按钮打开,计算供货价单价和供货总价
                if(supplierOpen !=null && supplierOpen == 1 && StringUtil.isNotBlank(price)){
                    Cell cell11 = row.createCell(10);
                    cell11.setCellStyle(style);
                    Integer supplierPrice = PriceUtils.mul2Integer(roleDiscount,price);
                    cell11.setCellValue(supplierPrice);
                    Cell cell12 = row.createCell(11);
                    cell12.setCellStyle(style);
                    cell12.setCellValue(PriceUtils.mul2Integer(num,supplierPrice.toString()));
                    //供货价总计
                    totalSupplierPrice = PriceUtils.add2Integer(totalSupplierPrice.toString(),String.valueOf(cell12.getNumericCellValue()),0);
                }
                //备注
                if(supplierOpen == null){
                    Cell cell11 = row.createCell(10);
                    cell11.setCellStyle(style);
                    cell11.setCellValue("");
                }else{
                    Cell cell13 = row.createCell(12);
                    cell13.setCellStyle(style);
                    cell13.setCellValue("");
                }
                //数量总计
                totalNum = PriceUtils.add2Integer(totalNum.toString(), num,0);
                //销售价总计
                totalMoney = PriceUtils.add2Integer(totalMoney.toString(), String.valueOf(cell10.getNumericCellValue()),0);

            }
            Row totalRow = sheet.getRow(rows + 1);
            Cell totalNumCell = totalRow.createCell(7);
            totalNumCell.setCellValue(totalNum);
            totalNumCell.setCellStyle(style);
            Cell totalMoneyCell = totalRow.createCell(9);
            totalMoneyCell.setCellValue(totalMoney);
            totalMoneyCell.setCellStyle(style);
            if(supplierOpen !=null && supplierOpen == 1){
                Cell totalSupplierPriceCell = totalRow.createCell(11);
                totalSupplierPriceCell.setCellValue(totalSupplierPrice);
                totalSupplierPriceCell.setCellStyle(style);
            }
            OutputStream outputStream = response.getOutputStream();
            response.setContentType("application/msexcel");
            String title = String.format("%s.xls", "产品销售表");
            response.addHeader("Content-Disposition", "filename=" + URLDecoder.decode(title, GloabConst.Chartset.UTF8));
//            OutputStream outputStream = response.getOutputStream();
//            File tempFile = File.createTempFile("temp",".xls");
//            FileOutputStream outputStream = new FileOutputStream(tempFile);
            wb.write(outputStream);
            outputStream.close();
            wb.close();
        } catch (Exception e) {
            e.printStackTrace();
            throw new LaiKeAPIException(ErrorCode.BizErrorCode.FILE_EXCEL_ERROR, "EXCEL文件导出失败", "excelExport");
        }

    }


    private static byte[] readInputStream(InputStream inStream) throws Exception {
        ByteArrayOutputStream outStream = new ByteArrayOutputStream();

        byte[] buffer = new byte[1024];

        int len = 0;

        while ((len = inStream.read(buffer)) != -1)
        {
            outStream.write(buffer, 0, len);
        }

        inStream.close();

        return outStream.toByteArray();
    }

}
